var express = require('express');
var mysql = require('mysql');
var bodyParser = require('body-parser');
var cookieParser = require('cookie-parser');
var session = require('express-session');
var multer = require('multer');
var crypto = require('crypto');

var server = express();
server.listen(8081);

// 静态文件
server.use(express.static('www/'));

// 设置目录
server.set('views','www/views/');
// 是否开启缓存
server.set('view cache',true);
// 模板的类型
server.set('view engine','ejs');

// 连接数据库
var db = mysql.createPool({
	host: 'localhost',
	port: 3306,
	user: 'root',
	password: '123456',
	database: 'dingzuo'
});

// 班级编号
var cNo = 0;

// 解析post数据
server.use(bodyParser.urlencoded({extended: true}));

// cookie以及session
server.use(cookieParser());

server.use(session({
	resave: true,
	saveUninitialized: false,
	secret: 'njdaksjdlakjdlk',
	// cookie: {maxAge: 3 * 60 * 1000} // 三分钟免登陆
	cookie: {maxAge: 7 * 24 * 60 * 60 * 1000}
}));
var classArr = [];
var tableArr = [];
// 文件上传
var obj = multer({dest: 'www/upload/'});
server.use(obj.any());
//监听任意接口
server.use(function (req,res,next) {
	
	var SQL = `show tables WHERE Tables_in_dingzuo LIKE 'aaa%'`;

 	db.query(SQL,function (err,data) {
 		classArr = [];
 		tableArr= [];
 		for (var i = 0;i < data.length;i++) {

 			var str = data[i].Tables_in_dingzuo.substr(3);
 			var str1 = data[i].Tables_in_dingzuo;
 			classArr.push(str);
 			tableArr.push(str1);
 		}
 	
 	});
	// console.log(req.session.token);
	if (req.session.token) { 
		//登录
		next();
	} else {
		// 没登录

		if (req.url == '/admin') {
			next();
		} else if (req.url == '/register') {
			next();
		} else {
			res.redirect('/admin');
		}
	}
});


// 显示班级界面
server.get('/create',function (req,res) {
	var token = req.session.token.username; 
	res.render('createClass',{username: token,manger: req.session.token.manger,classArr});
});

// 删除班级表格
server.get('/delete',function (req,res){
	var username = req.session.token.username; 
	var cNo = req.query.cNo;
	var SQL = `DROP TABLE IF EXISTS ${cNo}`;
 	db.query(SQL,function (err,data) {
 		// if (err) {
 		// 	res.render('allClass',{data: classArr,err: 1,msg: '服务器故障',username});
 		// } else {
 		// 	res.render('allClass',{data: classArr,err: 0,msg: '删除成功',username});
 		// }
 		res.redirect('/allClass');
 	});
})

// 所有班级
server.get('/allClass',function (req,res) {
	var username = req.session.token.username; 
	var SQL = `show tables WHERE Tables_in_dingzuo LIKE 'aaa%'`;
	var aaaArr = [];
 	db.query(SQL,function (err,data) {
 	
		for (var i = 0;i < data.length;i++) {

			var str = data[i].Tables_in_dingzuo.substr(3);
			
			aaaArr.push(str);
			
		}
 		res.render('allClass',{data: aaaArr,err: 0,msg: '',username,manger: req.session.token.manger});
 	});
	
});
// 创建班级表格接口
server.get('/createClass',function (req,res) {
	
	var cNo = req.query.cNo;
	var SQL = `DROP TABLE IF EXISTS ${cNo}`;
	db.query(SQL,function (err,data) {
	
		var SQL1 = `CREATE TABLE ${cNo} (
		  cellId int(11) NOT NULL,
		  sName varchar(255) DEFAULT NULL,
		  tName varchar(255) DEFAULT NULL,
		  stu int(11) DEFAULT NULL,
		  teacher int(11) DEFAULT NULL,
		  guodao int(11) DEFAULT NULL,
		  price int(11) DEFAULT NULL,
		  zixun varchar(255) DEFAULT NULL,
		  content varchar(255) DEFAULT NULL,
		  rows int(11) DEFAULT NULL,
		  cols int(11) DEFAULT NULL,
		  PRIMARY KEY (cellID)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8`;
			db.query(SQL1,function (err,data){
				if (!err) {
					res.send({err: 0,msg:'创建成功'});
				} else {
					res.send('创建失败');
				}
			})
			
	});
	
});

// 创建座位
server.get('/addSeat',function (req,res) {

	var {
					cellId,
					teacher,
					guodao,
					stu,
					tName,
					sName,
					price,
					zixun,
					content,
					rows,
					cols,cNo} = req.query;

	sName = sName.replace(/^\s\s*/, '').replace(/\s\s*$/, '');
	var SQL = `INSERT INTO ${cNo} (cellId,teacher,guodao,stu,tName,sName,price,zixun,content,rows,cols) VALUES ('${cellId}','${teacher}','${guodao}','${stu}','${tName}','${sName}','${price}','${zixun}','${content}','${rows}','${cols}')`;
	db.query(SQL,function (err,data) {
		if (err) {
			res.status(500);
			res.send({err: 1,msg: '服务器问题'});
		} else {

			res.send({err:0,msg: '插入成功'});
		}
	});
	
});

// 登录
server.get('/admin',function (req,res) {

	// 模板的名字   数据
	res.render('login',{err: 0});
});
// 注册
server.get('/register',function (req,res) {

	// 模板的名字   数据
	res.render('register',{err: 0});
});

// 返回首页
server.get('/',function (req,res) {
	var token = req.session.token.username;
	res.render('index',{username: token,manger: req.session.token.manger});
});

// 登录验证
server.post('/admin',function (req,res) {
	//{ username: '111', pwd: '2222' }
	var {username,pwd} = req.body;
	// 加密
	// 设置加密的方式
	var jiami = crypto.createHash('md5');
	// 加密
	jiami.update(pwd+'huayanghai');

	var jiaPWD = jiami.digest('hex');
	

	// var SQL = `INSERT INTO admin (name,pwd) VALUES ('${username}','${jiaPWD}')`;

	var SQL = `SELECT * FROM admin WHERE name='${username}' AND pwd='${jiaPWD}'`;

	// 从数据库里面查找
	db.query(SQL,function (err,data) {
		if (err) {
			console.log('数据库错误',err);
			res.status(500);
		} else {
			if (data.length > 0) {
				// 成功以后,记录成功的状态 (session)
				var manger = data[0].manger;
				req.session.token = {username,pwd,manger}; 
				// 重定向: 跳转到对应的接口里面
				// res.redirect('/admin/create');
				var token = req.session.token.username;

				res.render('index',{username: token,manger: req.session.token.manger});
			} else {
				// 
				res.render('login',{err: 1});
				// res.redirect('/admin');
			}
		}
	});
})


// 班级效果展示图

// 请求数据
server.get('/getData',function (req,res) {
	var cNo = req.query.cNo;
	db.query(`SELECT * FROM ${cNo} ORDER BY cellId`,function (err,data) {
		if (err) {
			res.send({err: 1,msg: '没有该班级'});
		} else {
			res.send({err: 0,data: data});
		}
	});
});

// 显示内容
server.get('/detail',function (req,res) {
	
	var token = req.session.token.username;
	var cNo = req.query.cNo;
	act = req.query.act;
	if (cNo) {
		// // 数据库里面的表信息
		db.query(`SELECT * FROM ${cNo} ORDER BY cellId`,function (err,data) {
			if (err) {
				if (act == 'm') {
					res.render('classDetail',{username: token,datas: data,err: 1,isShow: true,classArr: classArr,cNo,manger: req.session.token.manger});
				} else {
					res.render('classDetail',{username: token,datas: data,err: 1,isShow: false,classArr: classArr,cNo,manger: req.session.token.manger});
				}
				

				res.status(500);
				
			} else {
				

				if (act == 'm') {
					res.render('classDetail',{username: token,datas: data,err: 0,isShow: true,classArr: classArr,cNo,manger: req.session.token.manger});
				} else {
					res.render('classDetail',{username: token,datas: data,err: 0,isShow: false,classArr: classArr,cNo,manger: req.session.token.manger});
				}
				
			}
		});
	} else {
	
		if (act == 'm') {
			res.render('classDetail',{username: token,datas: [],err: 0,isShow: true,classArr: classArr,cNo,manger: req.session.token.manger});
		} else {
			res.render('classDetail',{username: token,datas: [],err: 0,isShow: false,classArr: classArr,cNo,manger: req.session.token.manger});
		}
	}
	
});




// 注册验证
server.post('/register',function (req,res) {
	//{ username: '111', pwd: '2222' }
	var {username,pwd} = req.body;

	var SQL = `SELECT * FROM admin WHERE name='${username}'`;
	// 在数据库里面查询:
	db.query(SQL,function (err,data) {
		if (err) {
			console.log('服务器出现故障',err);
		} else {
			// 有的情况下
			if (data.length > 0) {
				res.render('register',{err: '用户名已被占用'});
			} else {
				// 加密
				// 设置加密的方式
				var jiami = crypto.createHash('md5');
				// 加密
				jiami.update(pwd+'huayanghai');

				var jiaPWD = jiami.digest('hex');
	
				// 不存在该数据,插入数据库	
				var SQL1 = `INSERT INTO admin (name,pwd) VALUES ('${username}','${jiaPWD}')`;
							
				
				//添加数据库
				db.query(SQL1,function (err,data) {
					if (err) {
						console.log('插入失败');
					} else {
						res.redirect('/admin');
					}
				})
				
			}
		}
	})

})


// 更新信息

server.get('/update',function (req,res) {

	var {
					cellId,
					teacher,
					guodao,
					stu,
					tName,
					sName,
					price,
					zixun,
					content,
					cNo} = req.query;

	sName = sName.replace(/^\s\s*/, '').replace(/\s\s*$/, '');	
	var SQL = `UPDATE ${cNo} SET teacher='${teacher}',guodao='${guodao}',stu='${stu}',tName='${tName}',sName='${sName}',price='${price}',zixun='${zixun}',content='${content}' WHERE cellId='${cellId}'`;
	// 更新
	db.query(SQL,function (err,data) {
		if (err) {
			console.log(err);
			res.send({err:1,msg: '更新失败'});
			res.status(500);
			
		} else {
			// res.send('更新成功');
			// 重定向到显示的界面
			res.send({err:0,msg: '更新成功'});
		}
	});
});

// 权限管理
server.get('/quanxian',function (req,res) {

	var name = req.query.name;
	var token = req.session.token.username;
	if (name == 'admin') {
		var SQL = `SELECT * FROM admin WHERE not name='admin'`;
		db.query(SQL,function (err,data) {
			
			if (!err) {

				res.render('quanxian',{data: data,username: token,manger: req.session.token.manger});
			}
		});
		
	}

});

// 权限管理
server.post('/quanxian',function (req,res) {
	var {id,manger} = req.body;
	var SQL = `UPDATE admin SET manger='${manger}'WHERE id='${id}'`;

	// 更新
	db.query(SQL,function (err,data) {
		if (err) {
			res.send({err:1,msg: '权限更新失败'});
			res.status(500);
			
		} else {
			// res.send('更新成功');
			res.send({err:0,msg: '权限更新成功'});
		}
	});


})


// 退出登录
server.get('/loginout',function (req,res){
	// 去除session保存的token值
	req.session.token = null;
	res.redirect('/admin');
});

// 查找
server.get('/search',function (req,res) {
	console.log(tableArr)
	var arr = [];
	var promise1;
	var ssArr = [];
	var r = req.query.name;
	for (var i = 0;i < tableArr.length;i++) {
		var str = tableArr[i];
		var SQL = `SELECT * FROM ${str}`;
		promise1 = new Promise(function(resolve, reject) {
			(function (i){
				db.query(SQL,function (err,data) {
				
					var json = {tableName:tableArr[i],data: data };
					arr.push(json);
					resolve(arr);
				})
			})(i);
		});
	}
	promise1.then(function (s) {
		if (s.length == tableArr.length) {
			
			for (var j = 0;j < s.length;j++) {
				var data = s[j];
				var allArr = data.data;
				for (var m = 0;m < allArr.length;m++) {
				
						if (allArr[m]['sName'].indexOf(r) != -1) {

							ssArr.push(data['tableName']);
						}
					
				}
			}
			var ssArr1 = [];
			for (var i = 0;i < ssArr.length;i++) {
				
				if (ssArr1.indexOf(ssArr[i]) == -1) {
					ssArr1.push(ssArr[i]);
				}
			}
		
			// 显示搜索页面
			res.render('search',{data: ssArr1,username: req.session.token.username,manger: req.session.token.manger,r});
		}
			
		
	})
})































